"""
Case Type   : 连接
Case Name   : 通过JDBC连接数据库进行idle_in_transaction_session_timeou设置，idle_in_transaction状态，会话断开时间
Create At   : 2024/10/14
Owner       : @li-xin12345
Description :
    1、JDBC连接数据库；
    2、设置idle_in_transaction_session_timeou为30s；
    set idle_in_transaction_session_timeou to 30;
    3、开启事务，等待30s ,查看会话状态；
    begin;
    等待31s
    select now();
    4、清理环境
Expect      :
    1、成功
    2、成功
    3、会话断开
    5、成功
History     :
"""
import os
import time
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.ComThread import ComThread

pri_sh = CommonSH('PrimaryDbUser')


@unittest.skipIf(3 != pri_sh.get_node_num(), '非1+2环境不执行')
class SessionTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.constant = Constant()
        self.common = Common()
        self.pri_root_node = Node('PrimaryRoot')
        self.pri_user_node = Node('PrimaryDbUser')
        self.db_name = 'db_connect_case0355'
        self.user_name = 'u_connect_case0355'
        self.script_name = 'Guc_connectionauthentication_Case0355'
        self.dest_path = os.path.join(
            macro.DB_MY_PATH, 'guc_connect_Case0355').replace('\\', '/')

        self.log.info('--前置处理：创建用户--')
        cmd = f"drop user if exists {self.user_name} cascade;" \
              f"create user {self.user_name} " \
              f"password '{self.pri_user_node.db_password}';" \
              f"grant all privileges to {self.user_name};"
        res = pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, res,
                      f'执行失败: {text}')
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, res,
                      f'执行失败: {text}')
        self.assertIn(self.constant.ALTER_ROLE_SUCCESS_MSG, res,
                      f'执行失败: {text}')

    def test_connect(self):
        text = '----step1: JDBC连接数据库；expect: 成功----'
        text += '----step2: 设置idle_in_transaction_session_timeou为30s；' \
               'expect: 成功----'
        self.log.info(text)
        self.log.info('--步骤1、2于java脚本中实现')
        self.log.info('--远程复制java脚本至指定主机--')
        self.common.scp_file(self.pri_root_node,
                             f"{self.script_name}.java", self.dest_path)
        cmd = f'ls {self.dest_path}'
        res = self.pri_root_node.sh(cmd).result()
        self.assertIn(self.script_name, res, '远程复制java脚本至指定主机失败')

        self.log.info('--编译java工具--')
        self.pri_root_node.scp_put(macro.JDBC_PATH,
                                   f"{self.dest_path}/postgresql.jar")
        cmd = f"javac -encoding utf-8 -cp " \
              f"{os.path.join(self.dest_path, 'postgresql.jar')} " \
              f"{os.path.join(self.dest_path, f'{self.script_name}.java')}"
        self.log.info(cmd)
        result = self.pri_root_node.sh(cmd).result()
        self.log.info(result)
        self.assertEqual('', result, '编译java工具失败')

        self.log.info('--运行java脚本--')
        url_host_port = f"{self.pri_user_node.db_host}:" \
                        f"{self.pri_user_node.db_port}"
        cmd = f" java -cp {os.path.join(self.dest_path, 'postgresql.jar')}" \
              f":{self.dest_path} " \
              f"{self.script_name} {url_host_port} " \
              f"{self.pri_user_node.db_name} {self.user_name} " \
              f"{self.pri_user_node.db_password} 2>&1"
        self.log.info(cmd)
        sql_thread = ComThread(self.pri_root_node.sh, args=(cmd,))
        sql_thread.setDaemon(True)
        sql_thread.start()

        text = '----step3: java脚本线程实现（开启事务），等待30s ,查看会话状态；' \
               'expect: 会话断开----'
        time.sleep(2)
        query_sql = f"select * from pg_stat_activity " \
                    f"where usename = '{self.user_name}';"
        check_session_exist = pri_sh.execut_db_sql(query_sql)
        self.log.info(check_session_exist)
        self.assertIn('1 row', check_session_exist, '执行失败' + text)
        self.assertIn('idle in transaction', check_session_exist, '执行失败' + text)

        self.log.info('--等待30s，查看会话状态--')
        time.sleep(30)
        check_session_exist = pri_sh.execut_db_sql(query_sql)
        self.log.info(check_session_exist)
        self.assertIn('0 rows', check_session_exist, '执行失败' + text)

        sql_thread.join(60)
        res = sql_thread.get_result()
        self.log.info(res)

    def tearDown(self):
        text = '----step5: 清理环境;expect:成功----'
        self.log.info(text)
        text = f'----后置操作：删除用户----'
        self.log.info(text)
        cmd = f'drop user if exists {self.user_name} cascade;'
        res = pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, res,
                      f'执行失败: {text}')

        text = f'-----{os.path.basename(__file__)} end-----'
        self.log.info(text)
